clear all
capture log close
program drop _all
set more off
snapshot erase _all

*****************************************************************
*****************************************************************
*** Preparing Final Kiva Tables and Figures.do
***
*** This program prepares the following tables and figures (in this order):
***
*** 	Using Kiva project data:
***			Figure 2: Baseline Hazard Function, Kiva Projects
***			Table 1: Kiva Funding Rates, Flexible Parametric Regressions
***			Table 2: Kiva Funding Rates, Before and After Natural Disasters
***			Figure 3: Baseline Hazard Functions by US Share, Timing, and Funding Concentration
***			Table A2: Kiva Loan Funding Rates, Cox Hazard Estimates
***			Table A3: Summary Statistics
***			Table A4: Total Number of Kiva Loans Financed by Country, 2006 - 2010
***			Table A5: Correlation and Variances for Immigration Variables
***			Table A6: Robustness Checks, Subsamples
***
***		Using panel data on aid:
***			Table A1: Selectivity by Aid Flow, Panel Regressions			
***
***	
*****************************************************************
*****************************************************************

*** USER SHOULD CHANGE DIRECTORY TO DESIRED FILE PATH
cd "xxxxxxxxxxxxxxxxxxxxxx"

*****************************************************************
*****************************************************************
*** Read in the Kiva project data
*****************************************************************
*****************************************************************

use "Final Kiva Project-Level Data.dta", clear

*****************************************************************
*****************************************************************
*** Figure 2: Baseline Hazard Function, Kiva Projects
*****************************************************************
*****************************************************************

**************************************
*** Declare survival-time data
**************************************
stset hourstofund, failure(fail) id(business_id)

**************************************
*** Create survival function
**************************************
sts generate survival = s
gen log_survival = log(survival)

**************************************
*** Graph the baseline hazard function
**************************************
twoway scatter log_survival hourstofund, msymbol(circle_hollow)
graph export "Kiva Figure 2 - Baseline Hazard Function.pdf", as(pdf) replace

*** Export data for graph to excel
export excel  log_survival hourstofund using "Kiva Figure 2 - Data for Baseline Hazard Function.xlsx", firstrow(variables) replace

*****************************************************************
*****************************************************************
*** Table 1: Kiva Funding Rates, Flexible Parametric Regressions
*****************************************************************
*****************************************************************

**************************************
*** Drop loan country share variables if donors from that country never make up more than 25% of a loan
**************************************
foreach var of varlist loan_country_share* {
	quietly sum `var'
	if `r(max)' < .1 {
		drop `var'
	}
}

**************************************
*** Declare survival-time data
**************************************
stset hourstofund, failure(fail) id(business_id)

**************************************
*** Run regressions
**************************************

*** Table 1, Column 1
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop loan_country_share* c_* s_2-s_15 h_* m_* day_*, nolog df(6) scale(hazard) 
outreg2 using "Kiva Table 1", label auto(4) dec(3) stats(coef se) e(all) excel replace

*** Mark cases in the sample
gen full_sample = (e(sample))

*** Table 1, Column 2
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 h_* m_* day_*, nolog df(6) scale(hazard) 
outreg2 using "Kiva Table 1", label auto(4) dec(3) stats(coef se) e(all) excel append

*** Table 1, Column 3
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 r_* h_* m_* day_*, nolog df(6) scale(hazard) 
outreg2 using "Kiva Table 1", label auto(4) dec(3) stats(coef se) e(all) excel append

*** Table 1, Column 4
stset hourstofund [pweight=privaid_2001], failure(fail) id(business_id)
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 h_* m_* day_*, nolog df(6) scale(hazard) 
outreg2 using "Kiva Table 11", label auto(4) dec(3) stats(coef se) e(all) excel append


*****************************************************************
*****************************************************************
*** Table 2: Kiva Funding Rates, Before and After Natural Disasters
*****************************************************************
*****************************************************************

set more off
**************************************
*** Declare survival-time data
**************************************
stset hourstofund, failure(fail) id(business_id)

**************************************
*** Run the regressions
**************************************

*** Table 2, Column 1 - 1 to 30 days before
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 h_* m_* day_* if disaster_pre==1, nolog df(6) scale(hazard)
outreg2 using "Kiva Table 2", label auto(4) dec(3) stats(coef se) e(all) excel replace

*** Table 2, Column 2 - 0 to 30 days after
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 h_* m_* day_* if disaster_post30==1, nolog df(6) scale(hazard)
outreg2 using "Kiva Table 2", label auto(4) dec(3) stats(coef se) e(all) excel append

*** Table 2, Column 3 - 31 to 60 days after
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share  loan_country_share* s_2-s_15 h_* m_* day_* if disaster_post60==1, nolog df(6) scale(hazard)
outreg2 using "Kiva Table 2", label auto(4) dec(3) stats(coef se) e(all) excel append

*****************************************************************
*****************************************************************
*** Figure 3: Baseline Hazard Functions by US Share, Timing, and Funding Concentration
*****************************************************************
*****************************************************************

**************************************
*** Declare survival-time data
**************************************
stset hourstofund, failure(fail) id(business_id)


**************************************
*** Figure 2A - US Share
**************************************
sts generate s_usover70 = s if loan_country_share186 >= .70
sts generate s_usunder70 = s if loan_country_share186 < .70
sts generate s_usunder25 = s if loan_country_share186 < .25
foreach var of varlist s_usover70 s_usunder70 s_usunder25 {
	gen log_`var' = log(`var')
}
sort hourstofund
twoway line log_s_usover70 log_s_usunder70 log_s_usunder25 hourstofund, name(usshare, replace)

**************************************
*** Figure 2B - Pre and Post Recession
**************************************
sts generate s_norecession = s if recession == 0
sts generate s_recession = s if recession == 1
foreach var of varlist s_norecession s_recession {
	gen log_`var' = log(`var')
}
sort hourstofund
twoway line log_s_norecession log_s_recession hourstofund,  name(recession, replace)

**************************************
*** Figure 2C - Concentration (95)
**************************************
sts generate s_hunder95= s if concentration < .95
sts generate s_hover95 = s if concentration >= .95
foreach var of varlist s_hunder95 s_hover95 {
	gen log_`var' = log(`var')
}
sort hourstofund
twoway line log_s_hunder95 log_s_hover95 hourstofund, name(h95, replace)


**************************************
*** Figure 2D - Concentration (75)
**************************************
sts generate s_hunder75= s if concentration < .75
sts generate s_hover75 = s if concentration >= .75
foreach var of varlist s_hunder75 s_hover75 {
	gen log_`var' = log(`var')
}
sort hourstofund
twoway line log_s_hunder75 log_s_hover75 hourstofund, name(h75, replace)

**************************************
*** Combine graphs
**************************************
graph combine usshare recession h95 h75
graph export "Kiva Figure 3 - Baseline Hazard Functions by US Share, Recession, Concentration.pdf", as(pdf) replace


*****************************************************************
*****************************************************************
*** Table A2: Kiva Loan Funding Rates, Cox Hazard Estimates
*****************************************************************
*****************************************************************
set more off

**************************************
*** Declare hazard data
**************************************
stset hourstofund, failure(fail) id(business_id)

**************************************
*** Run regressions
**************************************
*** Table A2, Column 1 -- full sample
stcox loan loan2 number_of_borrowers female loan_duration loan_country_share186 concentration risk_rating c_* s_2-s_15 h_* m_* day_*, nohr
local ll  = `e(ll)'
estat phtest
local prob = 1-chi2(`r(df)', `r(chi2)')
outreg2 using "Kiva Table A2", label auto(4) dec(3) addstat(Ln(L), `ll',  `"Grambsh-Therneau"', `prob') excel replace

*** Table A2, Column 2 -- 1st quartile
quietly sum hourstofund, det
stcox loan loan2 number_of_borrowers female loan_duration loan_country_share186 concentration risk_rating c_* s_2-s_15 h_* m_* day_* if hourstofund <= `r(p25)', nohr
local ll  = `e(ll)'
estat phtest
local prob = 1-chi2(`r(df)', `r(chi2)')
outreg2 using "Kiva Table A2", label auto(4) dec(3) addstat(Ln(L), `ll', `"Grambsh-Therneau"', `prob') excel append

*** Table A2, Column 3 -- 2nd quartile
quietly sum hourstofund, det
stcox loan loan2 number_of_borrowers female loan_duration loan_country_share186 concentration risk_rating c_* s_2-s_15 h_* m_* day_* if hourstofund > `r(p25)' & hourstofund <= `r(p50)', nohr
local ll  = `e(ll)'
estat phtest
local prob = 1-chi2(`r(df)', `r(chi2)')
outreg2 using "Kiva Table A2", label auto(4) dec(3) addstat(Ln(L), `ll', "Grambsh-Therneau", `prob') excel append

*** Table A2, Column 4 -- 3rd quartile
quietly sum hourstofund, det
stcox loan loan2 number_of_borrowers female loan_duration loan_country_share186 concentration risk_rating c_* s_2-s_15 h_* m_* day_* if hourstofund > `r(p50)' & hourstofund <= `r(p75)', nohr
local ll  = `e(ll)'
estat phtest
local prob = 1-chi2(`r(df)', `r(chi2)')
outreg2 using "Kiva Table A2", label auto(4) dec(3) addstat(Ln(L), `ll', "Grambsh-Therneau", `prob') excel append

*** Table A2, Column 5 -- 4th quartile
quietly sum hourstofund, det
stcox loan loan2 number_of_borrowers female loan_duration loan_country_share186 concentration risk_rating c_* s_2-s_15 h_* m_* day_* if hourstofund > `r(p75)' & hourstofund <= `r(max)', nohr
local ll  = `e(ll)'
estat phtest
local prob = 1-chi2(`r(df)', `r(chi2)')
outreg2 using "Kiva Table A2", label auto(4) dec(3) addstat(Ln(L), `ll', "Grambsh-Therneau", `prob') excel append


*****************************************************************
*****************************************************************
*** Table A3: Summary Statistics
*****************************************************************
*****************************************************************

tabstat hourstofund loan number_of_borrowers female loan_duration concentration loan_country_share186 risk_rating djichange num_articles ///
	affected_90 population cpiagov ODApercap official_microcredit affinity gdp_pc number_immigrant remit sharepost2000 ///
	rellpc_income refugee_imm deaths ///
	if full_sample == 1, col(stats) stats(n mean sd min max) format(%16.2fc)

*****************************************************************
*****************************************************************	
*** Table A4: Total Number of Kiva Loans Financed by Country, 2006 - 2010
*****************************************************************
*****************************************************************

preserve
contract country if full_sample == 1
list
export excel using "Kiva Table A4.xlsx", replace
restore

*****************************************************************as
*****************************************************************
*** Table A5: Correlation and Variances for Immigration Variables
*****************************************************************
*****************************************************************

**************************************
*** Run pairwise correlations
**************************************
mkcorr loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income refugee_share, ///
	sig replace cdec(3) log("Kiva Table A5.log")

**************************************
*** Run covariances
**************************************
putexcel set "Kiva Table A5.xlsx", sheet("Covariances", replace) modify
correlate loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income refugee_share, covariance
matrix cov = r(C)
putexcel A1 = matrix(cov), names

*****************************************************************
*****************************************************************
*** Table A6:  Robustness Checks, Subsamples
*****************************************************************
*****************************************************************
set more off

**************************************
*** Declare survival time data
**************************************
stset hourstofund, failure(fail) id(business_id)

**************************************
*** By US share
**************************************

*** Table A6, Column 1 -- >=70%
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 r_* h_* m_* day_* ///
	if loan_country_share186 >= .7 & loan_country_share186 < ., nolog df(6) scale(hazard) 
outreg2 using "Kiva Table A4", label auto(4) dec(3) stats(coef se) e(all) excel replace

*** Table A6, Column 2 -- <= 25%
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 r_* h_* m_* day_* ///
	if loan_country_share186 <= .25, nolog df(6) scale(hazard) 
outreg2 using "Kiva Table A4", label auto(4) dec(3) stats(coef se) e(all) excel append

**************************************
*** By timing
**************************************

*** Table A6, Column 3 -- Pre recession
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 r_* h_* m_* day_* ///
	if recession == 0, nolog df(6) scale(hazard) 
outreg2 using "Kiva Table A4", label auto(4) dec(3) stats(coef se) e(all) excel append

*** Table A6, Column 4 -- Post recession
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 r_* h_* m_* day_* ///
	if recession == 1, nolog df(6) scale(hazard) 
outreg2 using "Kiva Table A4", label auto(4) dec(3) stats(coef se) e(all) excel append

**************************************
*** By concentration
**************************************

*** Table A6, Column 5 -- >= 95%
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 r_* h_* m_* day_* ///
	if concentration >= .95 & concentration < ., nolog df(6) scale(hazard) 
outreg2 using "Kiva Table A4", label auto(4) dec(3) stats(coef se) e(all) excel append

***  Table A6, Column 6 -- < 95%
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 r_* h_* m_* day_* ///
	if concentration < .95, nolog df(6) scale(hazard) 
outreg2 using "Kiva Table A4", label auto(4) dec(3) stats(coef se) e(all) excel append

*** Table A6, Column 7 -- >= 75%
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 r_* h_* m_* day_* ///
	if concentration >= .75 & concentration < ., nolog df(6) scale(hazard) 
outreg2 using "Kiva Table A4", label auto(4) dec(3) stats(coef se) e(all) excel append

***  Table A6, Column 8 -- < 75%
stpm2 loan loan2 number_of_borrowers female loan_duration risk_rating djichange ln_num_articles lamount_perweek laffected_90 lpop logcgdppc cpiagov lODApercap ln_deaths affinity ln_number_immigrant ln_remit sharepost2000 rellpc_income  refugee_share loan_country_share* s_2-s_15 r_* h_* m_* day_* ///
	if concentration < .75, nolog df(6) scale(hazard) 
outreg2 using "Kiva Table A4", label auto(4) dec(3) stats(coef se) e(all) excel append



*****************************************************************
*****************************************************************
*** Read in the country-year panel data on aid
*****************************************************************
*****************************************************************

use  "Final Kiva Country-Year Panel Data.dta", clear 

*****************************************************************
*****************************************************************
*** Table A1: Selectivity by Aid Flow, Panel Regressions
*****************************************************************
*****************************************************************

**************************************
*** Run regressions
**************************************
encode recipient, gen(recipient_num)
xtset recipient_num year

*** All data

reg ln_oda ln_pop ln_gdp cpiagov, cluster(recipient_num)
local p_f = 1-F(`e(df_m)', `e(df_r)', `e(F)')
disp `p_f'
outreg2 using "Table A1", dec(3) excel addstat("n", `e(N_clust)', "Prob > F", `p_f' )  replace

reg ln_microcredit ln_pop ln_gdp cpiagov, cluster(recipient_num)
local p_f = 1-F(`e(df_m)', `e(df_r)', `e(F)')
disp `p_f'
outreg2 using "Table A1", dec(3) excel addstat("n", `e(N_clust)', "Prob > F", `p_f' )  append

reg ln_kiva_amt ln_pop ln_gdp cpiagov, cluster(recipient_num)
local p_f = 1-F(`e(df_m)', `e(df_r)', `e(F)')
disp `p_f'
outreg2 using "Table A1", dec(3) excel addstat("n", `e(N_clust)', "Prob > F", `p_f' )  append

*** Countries with data for all three types of flows
gen all_flows = (ln_oda < . & ln_microcredit < . & ln_kiva_amt < .)

reg ln_oda ln_pop ln_gdp cpiagov if all_flows == 1, cluster(recipient_num)
local p_f = 1-F(`e(df_m)', `e(df_r)', `e(F)')
disp `p_f'
outreg2 using "Table A1", dec(3) excel addstat("n", `e(N_clust)', "Prob > F", `p_f' )  append

reg ln_microcredit ln_pop ln_gdp cpiagov if all_flows == 1, cluster(recipient_num)
local p_f = 1-F(`e(df_m)', `e(df_r)', `e(F)')
disp `p_f'
outreg2 using "Table A1", dec(3) excel addstat("n", `e(N_clust)', "Prob > F", `p_f' )  append

reg ln_kiva_amt ln_pop ln_gdp cpiagov if all_flows == 1, cluster(recipient_num)
local p_f = 1-F(`e(df_m)', `e(df_r)', `e(F)')
disp `p_f'
outreg2 using "Table A1", dec(3) excel addstat("n", `e(N_clust)', "Prob > F", `p_f' )  append

